package cn.mysdp.biz.facade;

import cn.mysdp.biz.domain.*;
import cn.mysdp.biz.dto.request.*;
import cn.mysdp.biz.dto.response.SdpWorkspaceForGetColumnListResponse;
import cn.mysdp.biz.dto.response.SdpWorkspaceForGetTableListResponse;
import cn.mysdp.biz.dto.response.SdpWorkspaceQueryResponse;
import cn.mysdp.biz.repository.*;
import cn.mysdp.utils.ConnectUtil;
import cn.mysdp.utils.JavaBeansUtil;
import cn.mysdp.utils.SplitUtil;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.parser.Feature;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.mysql.cj.protocol.Resultset;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.annotations.Results;
import org.mybatis.generator.api.ConnectionFactory;
import org.mybatis.generator.api.IntrospectedColumn;
import org.mybatis.generator.api.IntrospectedTable;
import org.mybatis.generator.api.JavaTypeResolver;
import org.mybatis.generator.api.dom.java.FullyQualifiedJavaType;
import org.mybatis.generator.config.*;
import org.mybatis.generator.internal.JDBCConnectionFactory;
import org.mybatis.generator.internal.ObjectFactory;
import org.mybatis.generator.internal.db.ActualTableName;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.*;

import static cn.mysdp.biz.facade.ProcessSQLFacadeImpl.*;
import static cn.mysdp.utils.ConnectUtil.decryptDbPassword;
import static org.springframework.jdbc.support.JdbcUtils.closeResultSet;

/**
 * @ClassName:
 * @Description: FacadeCustomImpl
 * @Author: SDP
 * @Date: 2021-08-30
 * @Version: 1.0
 * Table:
 * Comment:
 *
 */
@Slf4j
public class SdpWorkspaceFacadeCustomImpl extends SdpWorkspaceFacadeBaseImpl {

    @Value("${spring.datasource.url}")
    private String springDatasourceUrl;

    @Value("${spring.datasource.username}")
    private String springDatasourceUsername;

    @Value("${spring.datasource.password}")
    private String springDatasourcePassword;

    @Autowired
    DataSource dataSource;

    @Autowired
    SdpWorkspaceMapper sdpWorkspaceMapper;

    @Autowired
    SdpProjectMapper sdpProjectMapper;

    @Autowired
    SdpWorkspaceConfigMapper sdpWorkspaceConfigMapper;

    @Autowired
    SdpTemplateMapper sdpTemplateMapper;

    @Autowired
    SdpSqlMapper sdpSqlMapper;

    @Autowired
    SdpHistoryMapper sdpHistoryMapper;

    @Autowired
    ProcessSQLFacade processSQLFacade;

    @Autowired
    SdpWorkspaceConfigFacade sdpWorkspaceConfigFacade;

    @Autowired
    SdpProjectFacade sdpProjectFacade;

    @Autowired
    SdpTemplateFacade sdpTemplateFacade;

    @Autowired
    SdpSqlFacade sdpSqlFacade;

    @Transactional(rollbackFor = Exception.class)
    @Override
    public Integer addSdpWorkspace(SdpWorkspaceAddRequest request) throws Exception {
        checkLowercaseName(request.getName());

        if (StringUtils.isEmpty(request.getName())) {
            throw new Exception("缺少名称：" + request.getName());
        }
        if (request.getName().indexOf('.') >= 0) {
            throw new Exception("名称不可包含小数点");
        }
        request.setDbPassword(processSQLFacade.encryptDBPassword(request));
        return super.addSdpWorkspace(request);
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public Integer updateSdpWorkspace(SdpWorkspaceUpdateRequest request) throws Exception {

        connectionMap.remove(request.getName());
        contextMap.remove(request.getName());
        javaTypeResolverMap.remove(request.getName());

        checkLowercaseName(request.getName());

        request.setDbPassword(processSQLFacade.encryptDBPassword(request));

        SdpWorkspaceWithBLOBs record = sdpWorkspaceMapper.selectByPrimaryKeyWithBLOBs(request.getId());
        if (record == null) {
            throw new Exception("not exist：" + request.getId());
        }

        String leadUrl = request.getDbJdbcurl();
        if(StringUtils.hasText(leadUrl)) {
            leadUrl = leadUrl.split("\\?")[0];
            if(StringUtils.hasText(record.getDbJdbcurl()) && !leadUrl.equals(record.getDbJdbcurl().split("\\?")[0])) {
                needRestart = "修改了连接配置，必须重启服务";
            }
        }
        if(StringUtils.hasText(request.getDbDatabase()) && StringUtils.hasText(record.getDbDatabase()) && !request.getDbDatabase().equals(record.getDbDatabase())) {
            needRestart = "修改了数据库配置，必须重启服务";
        }

        if (Integer.valueOf(0).equals(request.getDbPort())) {
            record.setDbPort(null);
            sdpWorkspaceMapper.updateByPrimaryKeyWithBLOBs(record);
            request.setDbPort(null);
        }
        if (!StringUtils.isEmpty(request.getName())) {
            if (request.getName().indexOf('.') >= 0) {
                throw new Exception("name invalid:include invalid character:'.'");
            }
            if (!request.getName().equals(record.getName())) {
                SdpWorkspaceConfigExample destExample = new SdpWorkspaceConfigExample();
                destExample.createCriteria().andWorkspaceNameEqualTo(record.getName());
                List<SdpWorkspaceConfigWithBLOBs> destList = sdpWorkspaceConfigMapper.selectByExampleWithBLOBs(destExample);

                SdpWorkspaceConfigWithBLOBs destBatchUpdateRequest = new SdpWorkspaceConfigWithBLOBs();
                destBatchUpdateRequest.setWorkspaceName(request.getName());

                sdpWorkspaceConfigMapper.updateByExampleSelective(destBatchUpdateRequest, destExample);
            }

            if (!request.getName().equals(record.getName())) {
                SdpProjectExample destExample = new SdpProjectExample();
                destExample.createCriteria().andWorkspaceNameEqualTo(record.getName());
                List<SdpProjectWithBLOBs> destList = sdpProjectMapper.selectByExampleWithBLOBs(destExample);

                SdpProjectWithBLOBs destBatchUpdateRequest = new SdpProjectWithBLOBs();
                destBatchUpdateRequest.setWorkspaceName(request.getName());
                sdpProjectMapper.updateByExampleSelective(destBatchUpdateRequest, destExample);
            }

            if (!request.getName().equals(record.getName())) {
                SdpTemplateExample destExample = new SdpTemplateExample();
                destExample.createCriteria().andWorkspaceNameEqualTo(record.getName());
                List<SdpTemplateWithBLOBs> destList = sdpTemplateMapper.selectByExampleWithBLOBs(destExample);

                SdpTemplateWithBLOBs destBatchUpdateRequest = new SdpTemplateWithBLOBs();
                destBatchUpdateRequest.setWorkspaceName(request.getName());

                sdpTemplateMapper.updateByExampleSelective(destBatchUpdateRequest, destExample);
            }

            if (!request.getName().equals(record.getName())) {
                SdpSqlExample destExample = new SdpSqlExample();
                destExample.createCriteria().andWorkspaceNameEqualTo(record.getName());
                List<SdpSqlWithBLOBs> destList = sdpSqlMapper.selectByExampleWithBLOBs(destExample);

                SdpSqlWithBLOBs destBatchUpdateRequest = new SdpSqlWithBLOBs();
                destBatchUpdateRequest.setWorkspaceName(request.getName());

                sdpSqlMapper.updateByExampleSelective(destBatchUpdateRequest, destExample);
            }
        }

        return super.updateSdpWorkspace(request);
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public Integer deleteSdpWorkspace(SdpWorkspaceDeleteRequest request) throws Exception {
        BaseRequest baseRequest = new BaseRequest();
        BeanUtils.copyProperties(request, baseRequest);

        for (int id : request.getIdList()) {
            SdpWorkspaceWithBLOBs record = sdpWorkspaceMapper.selectByPrimaryKeyWithBLOBs(id);

            if (record == null || StringUtils.isEmpty(record.getName())) {
                continue;
            }

            connectionMap.remove(record.getName());
            contextMap.remove(record.getName());
            javaTypeResolverMap.remove(record.getName());

            {
                SdpSqlExample example = new SdpSqlExample();
                example.createCriteria().andWorkspaceNameEqualTo(record.getName());
                List<SdpSqlWithBLOBs> list1 = sdpSqlMapper.selectByExampleWithBLOBs(example);
                SdpSqlDeleteRequest destDeleteRequest = new SdpSqlDeleteRequest();
                destDeleteRequest.setIdList(new ArrayList<>());
                for (SdpSqlWithBLOBs record1 : list1) {
                    if ("sql".equals(record1.getParameterCatalog())) {
                        continue;
                    }
                    destDeleteRequest.getIdList().add(record1.getId());
                }
                if (destDeleteRequest.getIdList().size() > 0) {
                    sdpSqlFacade.deleteSdpSql(destDeleteRequest);
                    Thread.sleep(1000);
                    destDeleteRequest.getIdList().clear();
                }
                for (SdpSqlWithBLOBs record1 : list1) {
                    if (!"sql".equals(record1.getParameterCatalog())) {
                        continue;
                    }
                    destDeleteRequest.getIdList().add(record1.getId());
                }
                if (destDeleteRequest.getIdList().size() > 0) {
                    sdpSqlFacade.deleteSdpSql(destDeleteRequest);
                    Thread.sleep(1000);
                }
            }
            {
                SdpTemplateExample example = new SdpTemplateExample();
                example.createCriteria().andWorkspaceNameEqualTo(record.getName());

                SdpTemplateDeleteRequest destDeleteRequest = new SdpTemplateDeleteRequest();
                destDeleteRequest.setIdList(new ArrayList<>());
                List<SdpTemplateWithBLOBs> list1 = sdpTemplateMapper.selectByExampleWithBLOBs(example);
                for (SdpTemplateWithBLOBs record1 : list1) {
                    destDeleteRequest.getIdList().add(record1.getId());
                }

                if (destDeleteRequest.getIdList().size() > 0) {
                    sdpTemplateFacade.deleteSdpTemplate(destDeleteRequest);
                    Thread.sleep(1000);
                }
            }
            {
                SdpProjectExample example = new SdpProjectExample();
                example.createCriteria().andWorkspaceNameEqualTo(record.getName());
                SdpProjectDeleteRequest destDeleteRequest = new SdpProjectDeleteRequest();
                destDeleteRequest.setIdList(new ArrayList<>());
                List<SdpProjectWithBLOBs> list1 = sdpProjectMapper.selectByExampleWithBLOBs(example);
                for (SdpProjectWithBLOBs record1 : list1) {
                    destDeleteRequest.getIdList().add(record1.getId());
                }
                if (destDeleteRequest.getIdList().size() > 0) {
                    sdpProjectFacade.deleteSdpProject(destDeleteRequest);
                    Thread.sleep(1000);
                }
            }
            {
                boolean hasDelete = false;
                SdpWorkspaceConfigExample example = new SdpWorkspaceConfigExample();
                example.createCriteria().andWorkspaceNameEqualTo(record.getName());
                SdpWorkspaceConfigDeleteRequest destDeleteRequest = new SdpWorkspaceConfigDeleteRequest();
                destDeleteRequest.setIdList(new ArrayList<>());
                List<SdpWorkspaceConfigWithBLOBs> list1 = sdpWorkspaceConfigMapper.selectByExampleWithBLOBs(example);
                for (SdpWorkspaceConfigWithBLOBs record1 : list1) {
                    destDeleteRequest.getIdList().add(record1.getId());
                }
                if (destDeleteRequest.getIdList().size() > 0) {
                    sdpWorkspaceConfigFacade.deleteSdpWorkspaceConfig(destDeleteRequest);
                    Thread.sleep(1000);
                }
            }
        }
        return super.deleteSdpWorkspace(request);
    }

    @Override
    public List<SdpWorkspaceQueryResponse> listSdpWorkspace(SdpWorkspaceQueryRequest request) throws Exception {
        List<SdpWorkspaceQueryResponse> list = super.listSdpWorkspace(request);
        for (SdpWorkspaceQueryResponse item : list) {
            if (!StringUtils.isEmpty(item.getDbPassword())) {
                item.setDbPassword("******");
            }
        }
        return list;
    }

    @Override
    public Integer cloneWorkspace(BaseNameIdRequest request) throws Exception {
        checkLowercaseName(request.getName());

        SdpWorkspaceWithBLOBs sdpWorkspace = sdpWorkspaceMapper.selectByNameWithBLOBs(request.getName());

        if (sdpWorkspace != null) {
            throw new Exception("dest name exist:" + request.getName());
        }

        sdpWorkspace = sdpWorkspaceMapper.selectByPrimaryKeyWithBLOBs(request.getId());
        if (request.getName().equals(sdpWorkspace.getName())) {
            throw new Exception("dest name is it self");
        }

        String oriName = sdpWorkspace.getName();

        sdpWorkspace.setName(request.getName());
        sdpWorkspace.setId(null);
        sdpWorkspaceMapper.insertSelective(sdpWorkspace);

        {
            SdpProjectExample example = new SdpProjectExample();
            example.createCriteria().andWorkspaceNameEqualTo(oriName);
            List<SdpProjectWithBLOBs> list = sdpProjectMapper.selectByExampleWithBLOBs(example);
            for (SdpProjectWithBLOBs item : list) {
                item.setWorkspaceName(request.getName());
                item.setId(null);
                sdpProjectMapper.insertSelective(item);
            }

        }

        {
            SdpWorkspaceConfigExample example = new SdpWorkspaceConfigExample();
            example.createCriteria().andWorkspaceNameEqualTo(oriName);
            List<SdpWorkspaceConfigWithBLOBs> list = sdpWorkspaceConfigMapper.selectByExampleWithBLOBs(example);
            for (SdpWorkspaceConfigWithBLOBs item : list) {
                item.setWorkspaceName(request.getName());
                item.setId(null);
                sdpWorkspaceConfigMapper.insertSelective(item);
            }

        }

        {
            SdpTemplateExample example = new SdpTemplateExample();
            example.createCriteria().andWorkspaceNameEqualTo(oriName);
            List<SdpTemplateWithBLOBs> list = sdpTemplateMapper.selectByExampleWithBLOBs(example);
            for (SdpTemplateWithBLOBs item : list) {
                item.setWorkspaceName(request.getName());
                item.setId(null);
                sdpTemplateMapper.insertSelective(item);
            }

        }

        {
            SdpSqlExample example = new SdpSqlExample();
            example.createCriteria().andWorkspaceNameEqualTo(oriName);
            List<SdpSqlWithBLOBs> list = sdpSqlMapper.selectByExampleWithBLOBs(example);
            for (SdpSqlWithBLOBs item : list) {
                item.setWorkspaceName(request.getName());
                item.setId(null);
                sdpSqlMapper.insertSelective(item);
            }

        }

        return 1;
    }

    @Override
    public Integer clone(BaseNameIdRequest request) throws Exception {
        checkLowercaseName(request.getName());

        SdpWorkspaceWithBLOBs sdpWorkspace = sdpWorkspaceMapper.selectByPrimaryKeyWithBLOBs(request.getId());

        SdpWorkspaceWithBLOBs destSdpWorkspace = sdpWorkspaceMapper.selectByNameWithBLOBs(request.getName());

        if (destSdpWorkspace == null) {
            SdpWorkspaceAddRequest addRequest = new SdpWorkspaceAddRequest();
            BeanUtils.copyProperties(sdpWorkspace, addRequest);
            addRequest.setName(request.getName());
            return addSdpWorkspace(addRequest);
        }
        SdpWorkspaceUpdateRequest updateRequest = new SdpWorkspaceUpdateRequest();
        BeanUtils.copyProperties(sdpWorkspace, updateRequest);
        updateRequest.setId(destSdpWorkspace.getId());
        updateRequest.setName(request.getName());
        updateSdpWorkspace(updateRequest);
        SdpWorkspaceWithBLOBs newRecord = new SdpWorkspaceWithBLOBs();
        newRecord.setId(destSdpWorkspace.getId());
        newRecord.setDbPassword(sdpWorkspace.getDbPassword());
        return sdpWorkspaceMapper.updateByPrimaryKeySelective(newRecord);
    }

    @Override
    public List<SdpWorkspaceForGetTableListResponse> getTableList(SdpWorkspaceForGetTableListRequest request) throws Exception {
        return _getTableList(request, null);
    }

    private List<SdpWorkspaceForGetTableListResponse> _getTableList(SdpWorkspaceForGetTableListRequest request, String tableName) throws Exception {
        if (StringUtils.isEmpty(request.getWorkspaceName())) {
            throw new Exception("缺少参数:workspace_name");
        }

        SdpWorkspaceWithBLOBs sdpWorkspace = sdpWorkspaceMapper.selectByNameWithBLOBs(request.getWorkspaceName());
        if (sdpWorkspace == null) {
            throw new Exception("无对应项目：" + request.getWorkspaceName());
        }

        List<SdpWorkspaceForGetTableListResponse> responseList = new ArrayList<>();

        Map<String, SdpWorkspaceForGetTableListResponse> doneNameMap = new HashMap<>();

        if (!"only_tables".equals(request.getCallType())) {
            SdpProjectExample sdpProjectExample = new SdpProjectExample();
            sdpProjectExample.createCriteria().andWorkspaceNameEqualTo(request.getWorkspaceName());
            List<SdpProjectWithBLOBs> sdpProjectWithBLOBs = sdpProjectMapper.selectByExampleWithBLOBs(sdpProjectExample);
            for (SdpProjectWithBLOBs project : sdpProjectWithBLOBs) {
                if (!StringUtils.isEmpty(project.getTables())) {
                    String[] tables = SplitUtil.split(project.getTables(), ",");
                    for (String table : tables) {
                        String[] names = SplitUtil.split(table, " as ");
                        table = names[0].trim().toLowerCase();
                        String alias = null;
                        if (names.length > 1) {
                            alias = names[1].trim().toLowerCase();
                        }
                        if (!doneNameMap.containsKey(table.split("-")[0])) {
                            SdpWorkspaceForGetTableListResponse newItem = new SdpWorkspaceForGetTableListResponse();
                            newItem.setName(table);
                            newItem.setAliasName(alias);
                            responseList.add(newItem);
                            doneNameMap.put(table.split("-")[0], newItem);
                        }
                    }
                }
            }
        }

        SdpWorkspaceQueryResponse workspace = new SdpWorkspaceQueryResponse();
        BeanUtils.copyProperties(sdpWorkspace, workspace);

        String url = ConnectUtil.getUrl(workspace);
        JDBCConnectionConfiguration jdbcConnectionConfiguration = new JDBCConnectionConfiguration();
        jdbcConnectionConfiguration.setDriverClass(workspace.getDbClassname());
        jdbcConnectionConfiguration.setConnectionURL(url);
        jdbcConnectionConfiguration.setUserId(workspace.getDbUsername());
        String dbPassword = processSQLFacade.decryptDbPassword(workspace);
        jdbcConnectionConfiguration.setPassword(dbPassword);

        Context context = new Context(null);
        context.setJdbcConnectionConfiguration(jdbcConnectionConfiguration);
        context.addProperty(PropertyRegistry.CONTEXT_BEGINNING_DELIMITER, "`");
        context.addProperty(PropertyRegistry.CONTEXT_ENDING_DELIMITER, "`");
        context.addProperty(PropertyRegistry.CONTEXT_AUTO_DELIMIT_KEYWORDS, "true");

        JavaTypeResolver javaTypeResolver = ObjectFactory
                .createJavaTypeResolver(context, new ArrayList<>());

        ConnectionFactory connectionFactory;

        jdbcConnectionConfiguration.addProperty("springDatasourceUrl", springDatasourceUrl);
        jdbcConnectionConfiguration.addProperty("springDatasourceUsername", springDatasourceUsername);
        jdbcConnectionConfiguration.addProperty("springDatasourcePassword", springDatasourcePassword);

        connectionFactory = new JDBCConnectionFactory(jdbcConnectionConfiguration);

        Connection connection = null;

        try {
            connection = connectionFactory.getConnection();

            if(StringUtils.hasText(workspace.getExtraInfo())) {
                try {
                    JSONObject extraInfo = JSONObject.parseObject(workspace.getExtraInfo());
                    String searchPath = extraInfo.getString("search_path");
                    if(StringUtils.hasText(searchPath)) {
                        jdbcConnectionConfiguration.addProperty("search_path", searchPath);
                        String sql = "set search_path to "+searchPath;
                        PreparedStatement psql = connection.prepareStatement(sql);
                        psql.execute();
                    }
                }catch (Exception ex) {
                    if((ex.getMessage()+"").contains("This connection has been closed")) {
                        processSQLFacade.closeConnection(workspace);
                    }
                    System.out.println("[ERROR]无法设置search_path:"+ex.getMessage());
                    ex.printStackTrace();

                }
            }
        } catch (Exception ex) {
            if((ex.getMessage()+"").contains("This connection has been closed")) {
                processSQLFacade.closeConnection(workspace);
            }
            ex.printStackTrace();
            if (ex instanceof SQLNonTransientConnectionException) {
                Throwable ex1 = ((SQLNonTransientConnectionException) ex).getCause();

                throw new Exception("cannot connect to database：" + workspace.getName() + ",db：" + workspace.getDbHost() + ":" + workspace.getDbPort() + "@" + workspace.getDbUsername() + ":" + workspace.getDbPassword() + "(" + ex1.getMessage() + ")");

            }
            throw new Exception("cannot connect to database：" + workspace.getName() + ",db：" + workspace.getDbHost() + ":" + workspace.getDbPort() + "@" + workspace.getDbUsername() + ":" + workspace.getDbPassword() + "(" + ex.getMessage() + ")");
        }

        DatabaseMetaData databaseMetaData = connection.getMetaData();

        Map<ActualTableName, List<IntrospectedColumn>> answer = new HashMap<>();

        String schema = ConnectUtil.getDatabaseName(workspace, connection);
        ResultSet rs = databaseMetaData.getTables(schema, null, "%", null);

        if (doneNameMap.size() > 0) {
            SdpWorkspaceForGetTableListResponse newItem = new SdpWorkspaceForGetTableListResponse();
            newItem.setName("---- tables not include in project ----");
            responseList.add(newItem);
        }
        Set<String> schemSet = new HashSet<>();
        Set<String> typeSet = new HashSet<>();
        while (rs.next()) {

            String _schem = rs.getString("TABLE_SCHEM");
            if(_schem != null) {
                _schem = _schem.trim().toLowerCase();
            }
            String type = rs.getString("TABLE_TYPE");
            if(type != null) {
                type = type.trim().toLowerCase();
                typeSet.add(type);
            }
            String cat = rs.getString("TABLE_CAT");
            if(cat != null) {
                cat = cat.trim().toLowerCase();
            }
            if("org.postgresql.Driver".equals(workspace.getDbClassname()) && "public".equals(_schem)) {
                if(!"table".equals(type)
                        && !"view".equals(type)
                ) {
                    continue;
                }

            } else if(StringUtils.hasText(_schem)) {
                schemSet.add(_schem);
                if(StringUtils.hasText(workspace.getDbDatabase())) {
                    if (!_schem.equals(workspace.getDbDatabase())) {
                        continue;
                    }
                }
            }
            String table = rs.getString("TABLE_NAME").trim().toLowerCase();
            String remarks = rs.getString("REMARKS");
            if (remarks != null) {
                remarks = remarks.split("\n")[0].split("\r")[0].trim();
            } else {
                remarks = "";
            }
            if (!doneNameMap.containsKey(table)) {
                SdpWorkspaceForGetTableListResponse newItem = new SdpWorkspaceForGetTableListResponse();
                newItem.setName(table);
                newItem.setRemark(remarks);
                responseList.add(newItem);
                doneNameMap.put(table, newItem);
            } else {
                doneNameMap.get(table).setRemark(remarks);
            }

        }

        closeResultSet(rs);



        return responseList;
    }

    @Override
    public String testConnect(SdpWorkspaceUpdateRequest request) throws Exception {
        SdpWorkspaceQueryResponse workspace = new SdpWorkspaceQueryResponse();
        if (request.getId() != null && !Integer.valueOf(0).equals(request.getId())) {
            SdpWorkspaceWithBLOBs oriWorkspace = sdpWorkspaceMapper.selectByPrimaryKeyWithBLOBs(request.getId());
            if (oriWorkspace == null) {
                throw new Exception("Not found workspace:" + request.getId());
            }
            BeanUtils.copyProperties(oriWorkspace, workspace);
            if (!StringUtils.isEmpty(oriWorkspace.getDbPassword())) {
                workspace.setDbPassword(processSQLFacade.decryptDbPassword(workspace));
            }
            if (request.getDbClassname() != null) {
                workspace.setDbClassname(request.getDbClassname());
            }
            if (request.getDbHost() != null) {
                workspace.setDbHost(request.getDbHost());
            }
            if (request.getDbPort() != null) {
                workspace.setDbPort(request.getDbPort());
            }
            if (request.getDbDatabase() != null) {
                workspace.setDbDatabase(request.getDbDatabase());
            }
            if (request.getDbUsername() != null) {
                workspace.setDbUsername(request.getDbUsername());
            }
            if (request.getDbPassword() != null) {
                workspace.setDbPassword(request.getDbPassword());
            }
            if (request.getDbJdbcurl() != null) {
                workspace.setDbJdbcurl(request.getDbJdbcurl());
            }
        } else {
            BeanUtils.copyProperties(request, workspace);
        }
        if (Integer.valueOf(0).equals(request.getDbPort())) {
            workspace.setDbPort(null);
        }

        String url = ConnectUtil.getUrl(workspace);
        JDBCConnectionConfiguration jdbcConnectionConfiguration = new JDBCConnectionConfiguration();
        jdbcConnectionConfiguration.setDriverClass(workspace.getDbClassname());
        jdbcConnectionConfiguration.setConnectionURL(url);
        jdbcConnectionConfiguration.setUserId(workspace.getDbUsername());
        String dbPassword = workspace.getDbPassword();
        jdbcConnectionConfiguration.setPassword(dbPassword);

        Context context = new Context(null);
        context.setJdbcConnectionConfiguration(jdbcConnectionConfiguration);
        context.addProperty(PropertyRegistry.CONTEXT_BEGINNING_DELIMITER, "`");
        context.addProperty(PropertyRegistry.CONTEXT_ENDING_DELIMITER, "`");
        context.addProperty(PropertyRegistry.CONTEXT_AUTO_DELIMIT_KEYWORDS, "true");

        JavaTypeResolver javaTypeResolver = ObjectFactory
                .createJavaTypeResolver(context, new ArrayList<>());

        Connection connection = null;

        try {
            jdbcConnectionConfiguration.addProperty("springDatasourceUrl", springDatasourceUrl);
            jdbcConnectionConfiguration.addProperty("springDatasourceUsername", springDatasourceUsername);
            jdbcConnectionConfiguration.addProperty("springDatasourcePassword", springDatasourcePassword);

            if ("org.h2.Driver".equals(workspace.getDbClassname())
                    && !StringUtils.hasText(workspace.getDbJdbcurl())
                    && !StringUtils.hasText(workspace.getDbDatabase())) {
                connection = dataSource.getConnection();
                context.setConnection(connection);
            } else {
                ConnectionFactory connectionFactory;
                connectionFactory = new JDBCConnectionFactory(jdbcConnectionConfiguration);

                connection = connectionFactory.getConnection();
                connection.close();
            }

        } catch (Exception ex) {
            ex.printStackTrace();
            if (ex instanceof SQLNonTransientConnectionException) {
                Throwable ex1 = ((SQLNonTransientConnectionException) ex).getCause();
                if (ex1 == null) {
                    throw new Exception("无法连接到数据库：数据库：" + workspace.getDbHost() + ":" + workspace.getDbPort() + "@" + workspace.getDbUsername() + "(" + ex.getMessage() + ")");
                }
                if (ex1.getMessage().indexOf("Access denied for user") >= 0) {
                    throw new Exception("数据库无权限或密码错误：数据库：" + workspace.getDbHost() + ":" + workspace.getDbPort() + "@" + workspace.getDbUsername() + "(" + ex1.getMessage() + ")" + "," + ex.getMessage());
                }

                throw new Exception("无法连接到数据库：数据库：" + workspace.getDbHost() + ":" + workspace.getDbPort() + "@" + workspace.getDbUsername() + "(" + ex1.getMessage() + ")" + "," + ex.getMessage());

            }
            throw new Exception("无法连接到数据库：数据库：" + workspace.getDbHost() + ":" + workspace.getDbPort() + "@" + workspace.getDbUsername() + ":" + workspace.getDbPassword() + "(" + ex.getMessage() + ")");
        }
        return "";
    }

    @Override
    public String fixJsonObject(BaseNameRequest request) throws Exception {
        JSONObject obj = JSONObject.parseObject(request.getName(), Feature.AllowUnQuotedFieldNames, Feature.AllowSingleQuotes);
        return JSONObject.toJSONString(obj);
    }

    @Override
    public List<SdpWorkspaceForGetColumnListResponse> getColumnList(SdpWorkspaceForGetColumnListRequest request) throws Exception {
        checkNonNull(request.getWorkspaceName(), "workspace_name");
//        checkNonNull(request.getTableName(), "table_name");

        List<SdpWorkspaceForGetColumnListResponse> responseList = new ArrayList<>();

        JSONObject resultJsonObject = new JSONObject();

        Map<String, Connection> connectionMap = new HashMap<>();
        Map<String, Context> contextMap = new HashMap<>();
        Map<String, JavaTypeResolver> javaTypeResolverMap = new HashMap<>();

        List<Map<String, String>> fieldInfo = new ArrayList<>();

        if(!StringUtils.hasText(request.getParameterSql())) {
            System.out.println("debugger");
        }
        {
            String workspaceName = request.getWorkspaceName();
            SdpWorkspaceWithBLOBs workspaceWithBLOBs = sdpWorkspaceMapper.selectByNameWithBLOBs(request.getWorkspaceName());

            SdpWorkspaceQueryResponse workspace = new SdpWorkspaceQueryResponse();
            BeanUtils.copyProperties(workspaceWithBLOBs, workspace);

            String url = ConnectUtil.getUrl(workspace);
            JDBCConnectionConfiguration jdbcConnectionConfiguration = new JDBCConnectionConfiguration();
            jdbcConnectionConfiguration.setDriverClass(workspace.getDbClassname());
            jdbcConnectionConfiguration.setConnectionURL(url);
            jdbcConnectionConfiguration.setUserId(workspace.getDbUsername());
            String dbPassword = null;
            if (StringUtils.isEmpty(dbPassword)) {
                dbPassword = decryptDbPassword(workspace);
            }
            jdbcConnectionConfiguration.setPassword(dbPassword);

            Context context = new Context(null);
            context.setJdbcConnectionConfiguration(jdbcConnectionConfiguration);
            context.setJavaModelGeneratorConfiguration(new JavaModelGeneratorConfiguration());
            context.addProperty(PropertyRegistry.CONTEXT_BEGINNING_DELIMITER, "`");
            context.addProperty(PropertyRegistry.CONTEXT_ENDING_DELIMITER, "`");
            context.addProperty(PropertyRegistry.CONTEXT_AUTO_DELIMIT_KEYWORDS, "true");

            contextMap.put(workspaceName, context);

            JavaTypeResolver javaTypeResolver = ObjectFactory
                    .createJavaTypeResolver(context, new ArrayList<>());

            javaTypeResolverMap.put(workspaceName, javaTypeResolver);

            Connection connection = null;

            try {
                jdbcConnectionConfiguration.addProperty("springDatasourceUrl", springDatasourceUrl);
                jdbcConnectionConfiguration.addProperty("springDatasourceUsername", springDatasourceUsername);
                jdbcConnectionConfiguration.addProperty("springDatasourcePassword", springDatasourcePassword);
                if ("org.h2.Driver".equals(workspace.getDbClassname())
                        && !StringUtils.hasText(workspace.getDbJdbcurl())
                        && StringUtils.isEmpty(workspace.getDbDatabase())) {
                    connection = dataSource.getConnection();
                    context.setConnection(connection);
                } else {
                    ConnectionFactory connectionFactory;
                    connectionFactory = new JDBCConnectionFactory(jdbcConnectionConfiguration);

                    connection = connectionFactory.getConnection();
                }

                if(StringUtils.hasText(workspace.getExtraInfo())) {
                    try {
                        JSONObject extraInfo = JSONObject.parseObject(workspace.getExtraInfo());
                        String searchPath = extraInfo.getString("search_path");
                        if(StringUtils.hasText(searchPath)) {
                            jdbcConnectionConfiguration.addProperty("search_path", searchPath);
                            String sql = "set search_path to "+searchPath;
                            PreparedStatement psql = connection.prepareStatement(sql);
                            psql.execute();
                        }
                    }catch (Exception ex) {
                        System.out.println("[ERROR]无法设置search_path:"+ex.getMessage());
                        ex.printStackTrace();

                    }
                }
            }catch(Exception ex) {
                ex.printStackTrace(System.err);
                if (ex instanceof SQLNonTransientConnectionException) {
                    Throwable ex1 = ((SQLNonTransientConnectionException)ex).getCause();
                    if (ex1 == null) {
                        throw new Exception("无法连接到数据库："+workspaceName+",数据库："+workspace.getDbHost()+":"+workspace.getDbPort()+"@"+workspace.getDbUsername()+"("+ex.getMessage()+")");
                    }
                    if (ex1.getMessage().indexOf("Access denied for user") >= 0) {
                        throw new Exception("数据库无权限或密码错误："+workspaceName+",数据库："+workspace.getDbHost()+":"+workspace.getDbPort()+"@"+workspace.getDbUsername()+"("+ex1.getMessage()+")"+","+ex.getMessage());
                    }

                    throw new Exception("无法连接到数据库："+workspaceName+",数据库："+workspace.getDbHost()+":"+workspace.getDbPort()+"@"+workspace.getDbUsername()+"("+ex1.getMessage()+")"+","+ex.getMessage());

                }
                throw new Exception("无法连接到数据库："+workspaceName+",数据库："+workspace.getDbHost()+":"+workspace.getDbPort()+"@"+workspace.getDbUsername()+":"+workspace.getDbPassword()+"("+ex.getMessage()+")");
            }

            Set<String> tableSet = new HashSet<>();

            if(StringUtils.hasText(request.getParameterSql())) {
                String parameterSql =  request.getParameterSql();
                String lowerSql = parameterSql.toLowerCase();

                PreparedStatement mysqlStatement = null;
                try {
                    String sql = parameterSql;
                    try {
                        mysqlStatement = connection.prepareStatement(sql);
                    } catch (Exception ex) {
                        if((ex.getMessage()+"").contains("This connection has been closed")) {
                            processSQLFacade.closeConnection(workspace);
                        }
                        ex.printStackTrace(System.err);
                        System.err.println("=== prepareStatement error, sql ===");
                        System.err.println(sql);
                        throw ex;
                    }
                    ResultSetMetaData resultSetMetaData;
                    try {
                        resultSetMetaData = mysqlStatement.getMetaData();
                    } catch (Exception ex) {
                        ex.printStackTrace(System.err);
                        System.err.println("=== getMetaData error, sql ===");
                        System.err.println(sql);
                        throw ex;
                    }

                    boolean noResultSetMetadata = false;
                    if(resultSetMetaData == null) {
                        noResultSetMetadata = true;
                        Statement mysqlStatement2 = connection.createStatement();
                        try {
                            mysqlStatement2.executeQuery("explain "+sql);
                            ResultSet resultSet = mysqlStatement2.getResultSet();
                            try {

                                JSONObject explainInfoRow = new JSONObject();
                                while(resultSet.next()) {
                                    ResultSetMetaData resultSetMetaData1 = resultSet.getMetaData();
                                    for(int c = 0; c < resultSetMetaData1.getColumnCount(); c++) {
                                        String n = resultSetMetaData1.getColumnName(c+1);
                                        if(!StringUtils.hasText(n)) {
                                            n = resultSetMetaData1.getColumnLabel(c+1);
                                        }
                                        if(!StringUtils.hasText(n)) {
                                            n = ""+(c+1);
                                        }
                                        explainInfoRow.put(n, resultSet.getObject(c+1));
                                    }
                                }
                                if(StringUtils.hasText(explainInfoRow.getString("table"))) {
                                    String dbName = connection.getCatalog();
                                    if(!StringUtils.hasText(dbName)) {
                                        dbName = connection.getSchema();
                                    }
                                    tableSet.add(dbName +"."+dbName+"."+explainInfoRow.getString("table"));

                                    mysqlStatement.close();
                                    try {
                                        mysqlStatement = connection.prepareStatement("select * from "+dbName+"."+explainInfoRow.getString("table"));
                                    } catch (Exception ex) {
                                        if((ex.getMessage()+"").contains("This connection has been closed")) {
                                            processSQLFacade.closeConnection(workspace);
                                        }
                                        ex.printStackTrace(System.err);
                                        System.err.println("=== prepareStatement error, sql ===");
                                        System.err.println(sql);
                                        throw ex;
                                    }
                                    try {
                                        resultSetMetaData = mysqlStatement.getMetaData();
                                    } catch (Exception ex) {
                                        ex.printStackTrace(System.err);
                                        System.err.println("=== getMetaData error, sql ===");
                                        System.err.println(sql);
                                        throw ex;
                                    }

                                }
                                resultJsonObject.put("explainInfo", explainInfoRow);

                            }finally {
                                resultSet.close();
                            }
                        }finally {
                            mysqlStatement2.close();
                        }
                    }


                    Object[] jdbcFields = ConnectUtil.getMetaFields(resultSetMetaData);
                    int oriColumnCount = jdbcFields.length;

                    Map<String, Method> methodMap = new HashMap<>();
                    methodMap.put("getName", null);
                    methodMap.put("getTableName", null);
                    methodMap.put("getOriginalTableName", null);
                    methodMap.put("getColumnName", null);
                    methodMap.put("getFullName", null);
                    methodMap.put("getOriginalColumnName", null);
                    methodMap.put("getDatabaseName", null);

                    Map<String, Field> fieldMap = new HashMap<>();
                    fieldMap.put("name", null);
                    fieldMap.put("tableName", null);
                    fieldMap.put("originalTableName", null);
                    fieldMap.put("columnName", null);
                    fieldMap.put("fullName", null);
                    fieldMap.put("originalColumnName", null);
                    fieldMap.put("databaseName", null);

                    Class clz = null;
                    if (jdbcFields.length > 0) {
                        clz = jdbcFields[0].getClass();
                    }
                    while (clz != null) {
                        int count = 0;
                        Method[] methods = jdbcFields[0].getClass().getDeclaredMethods();
                        for (Method method : methods) {
                            count = 0;
                            for (String fieldKey : methodMap.keySet()) {
                                if (methodMap.get(fieldKey) != null) {
                                    count++;
                                    continue;
                                }
                                if (fieldKey.equals(method.getName())) {
                                    method.setAccessible(true);
                                    methodMap.put(fieldKey, method);
                                    count++;
                                }
                            }
                            if (count == methodMap.size()) {
                                break;
                            }
                        }
                        if (count == methodMap.size()) {
                            break;
                        }
                        clz = clz.getSuperclass();
                    }

                    if (jdbcFields.length > 0) {
                        clz = jdbcFields[0].getClass();
                    }
                    while (clz != null) {
                        int count = 0;
                        Field[] fields = jdbcFields[0].getClass().getDeclaredFields();
                        for (Field field : fields) {
                            try {
                                if(!fieldMap.containsKey(field.getName()) || fieldMap.get(field.getName()) == null) {
                                    field.setAccessible(true);
                                    fieldMap.put(field.getName(), field);
                                }
                            }catch (Exception ex) {

                            }
                        }
                        clz = clz.getSuperclass();
                    }

                    for (Object field : jdbcFields) {
                        Map<String, String> fieldInfoMap = new HashMap<>();
                        String fieldOriginalTableName = null;
                        if(methodMap.get("getOriginalTableName") != null) {
                            fieldOriginalTableName = (String) methodMap.get("getOriginalTableName").invoke(field);
                            fieldInfoMap.put("originalTableName", fieldOriginalTableName);
                        }
                        if(methodMap.get("getOriginalColumnName") != null) {
                            fieldInfoMap.put("originalColumnName", (String)methodMap.get("getOriginalColumnName").invoke(field));
                        }
                        if(methodMap.get("getName") != null) {
                            fieldInfoMap.put("name", (String) methodMap.get("getName").invoke(field));
                        }
                        if(methodMap.get("getTableName") != null) {
                            fieldInfoMap.put("tableName", (String) methodMap.get("getTableName").invoke(field));
                        }
                        if(methodMap.get("getColumnName") != null) {
                            fieldInfoMap.put("columnName", (String) methodMap.get("getColumnName").invoke(field));
                        }
                        if(methodMap.get("getDatabaseName") != null) {
                            fieldInfoMap.put("databaseName", (String) methodMap.get("getDatabaseName").invoke(field));
                        }
                        if(StringUtils.hasText(fieldInfoMap.get("originalTableName"))) {
                            String dbName = fieldInfoMap.get("databaseName");
                            if(!StringUtils.hasText(dbName)) {
                                dbName = connection.getCatalog();
                            }
                            if(!StringUtils.hasText(dbName)) {
                                dbName = connection.getSchema();
                            }
                            tableSet.add(dbName +"."+dbName+"."+fieldInfoMap.get("originalTableName"));
                        }

                        for(String fieldName: fieldMap.keySet()) {
                            if (!StringUtils.hasText(fieldInfoMap.get(fieldName)) && fieldMap.get(fieldName) != null) {
                                Object v = fieldMap.get(fieldName).get(field);
                                if(v != null) {
                                    fieldInfoMap.put(fieldName, v.toString());
                                }
                            }
                        }

                        fieldInfo.add(fieldInfoMap);
                    }
                    resultJsonObject.put("fieldInfo", fieldInfo);

                } catch (Exception ex) {
                    ex.printStackTrace(System.err);
                    throw ex;
                } finally {
                    if (mysqlStatement != null) {
                        mysqlStatement.close();
                    }
                }
            }


            connectionMap.put(workspaceName, connection);
            System.out.println("connection for:"+workspaceName);

            if(StringUtils.hasText(request.getTableName())) {
                DatabaseMetaData databaseMetaData = connection.getMetaData();
                Field field = JavaBeansUtil.getField(databaseMetaData.getClass(), "database");
                String dbName = null;
                if(field != null) {
                    dbName = (String) field.get(databaseMetaData);
                    tableSet.add(dbName+"."+dbName+"."+request.getTableName());
                }
            }
            for(String name: tableSet) {
                TableConfiguration tc = new TableConfiguration(context);
                String[] names = name.split("\\.");
                if(names.length != 3) {
                    System.out.println("debugger");
                    continue;
                }
                if ("org.h2.Driver".equals(workspace.getDbClassname())) {
                    tc.setSchema("public");
                    tc.setCatalog("public");
                } else {
                    tc.setCatalog(names[1]);
                }
                tc.setTableName(names[2]);
                context.addTableConfiguration(tc);
            }

            List<String> warnings = new ArrayList<>();
            context.introspectTables(null,
                    warnings,
                    null);
            if (warnings.size() > 0) {
                for(String line: warnings) {
                    if (!line.startsWith("Warning")) {
                        throw new Exception("错误："+warnings.get(0));
                    }
                }
            }

            JSONObject tableInfo = new JSONObject();
            Field introspectedTablesField = cn.mysdp.utils.JavaBeansUtil.getField(context.getClass(), "introspectedTables");
            introspectedTablesField.setAccessible(true);
            List<IntrospectedTable> introspectedTables = (List<IntrospectedTable>) introspectedTablesField.get(context);
            for(IntrospectedTable introspectedTable: introspectedTables) {
                List<IntrospectedColumn> cols = new ArrayList<>();
                for(IntrospectedColumn c: introspectedTable.getAllColumns()) {
                    IntrospectedColumn col = new IntrospectedColumn();
                    BeanUtils.copyProperties(c, col);
                    col.setContext(null);
//                    col.setFieldInfoMap(null);
                    col.setIntrospectedTable(null);
                    col.setReplaceTableAnnotationMap(null);
                    cols.add(col);
                }
                tableInfo.put(workspaceName + "." + introspectedTable.getTableConfiguration().getCatalog() + "." + introspectedTable.getTableConfiguration().getTableName(), cols);
            }
            resultJsonObject.put("tableInfo", tableInfo);

        }


        for(String s: connectionMap.keySet()) {
            connectionMap.get(s).close();
        }
        responseList.add(new SdpWorkspaceForGetColumnListResponse());
//        responseList.get(0).setExtraInfo(resultJsonObject.toJSONString());
        responseList.get(0).setExtraInfo(JSONObject.toJSONString(resultJsonObject, SerializerFeature.DisableCircularReferenceDetect));

        return responseList;
    }

    @Override
    public Integer countForGetColumnList(SdpWorkspaceForGetColumnListRequest request) throws Exception {
        return getColumnList(request).size();
    }
}
